*------------------------------------------------------------------------------------------------------------%	
* Table 10: Estimated spillovers using the QCEW
*------------------------------------------------------------------------------------------------------------%	

	use "$data/qcew/qcew_cz_all_industries_final.dta", clear
	
	keep if inlist(industry_code,"44-45", "72", "48-49","56")
	keep if qtr==1
	
	keep total_qtrly_wages avg_emp  cz_total_qtrly_wages cz_avg_emp czone qdate qtr
	
	collapse (sum) total_qtrly_wages avg_emp (mean) cz_total_qtrly_wages cz_avg_emp , by(czone qdate qtr)
	
	rename czone cz
	rename qtr quarter
	tempfile qcew_totals
	save `qcew_totals', replace
	
	import excel using "$data/cb/events_qcew_expanded.xlsx", first clear
	
	foreach var in qdate qdate_pre qdate_post{
	rename `var' `var'_string
	gen `var' = quarterly(`var'_string, "YQ")
	format `var' %tq
	drop `var'_string
	}
	
	save "$data/cb/events_qcew_expanded.dta", replace

	use "$data/cb/events_qcew_expanded.dta", clear
	*local experiment=1
	levelsof eventid, local(exp)
	foreach experiment in `exp' {	
		* Keep relevant row
		use in `experiment' using "$data/cb/events_qcew_expanded.dta", clear
			
		* Store relevant parameters
		foreach vr in companies mw qdate qdate_pre qdate_post eventid  {
			local `vr'=`vr'[1]
		}
		local bmw=`mw'-1
		local amw=`mw'-1
		
		clear
		
		noi di in green "Experiment `experiment': " in yellow `"`companies' (`mw') "' in green "`qdate'"
		
		* Load quartlery CZ wage and employment data
		use "$data/cb/clean_policy_firm_q1_wagebill_cz.dta", clear
		
		* Fill in experiment parameters
		gen trt_exp= `eventid'
		gen etime=qdate-`qdate'
		gen mw=`mw'
		
		tostring cmp_company_code, gen(cmp_company_code_string)
		
		* Indicator for policy firm
		gen policy_firm=0
		foreach firm in `companies' {
			qui replace policy_firm=1 if cmp_company_code_string=="`firm'"
		}		
				
		keep if policy_firm==1
		keep if inrange(qdate,`qdate_pre', `qdate_post')
						
		collapse (sum) total_gross_pay_ytd freq_gross_pay_ytd,by(cz qdate etime trt_exp )
		gen avg_gross_pay_ytd=total_gross_pay_ytd/freq_gross_pay_ytd
		
		merge 1:1 cz qdate using `qcew_totals', keep (3) keepusing(total_qtrly_wages avg_emp cz_total_qtrly_wages cz_avg_emp)
		
		gen avg_wages = cz_total_qtrly_wages/cz_avg_emp
		gen avg_wages_ret_svc=total_qtrly_wages/avg_emp
		gen other_total_wages=cz_total_qtrly_wages-total_gross_pay_ytd
		gen other_total_wages_ret_svc= total_qtrly_wages-total_gross_pay_ytd

		gen other_emp=cz_avg_emp-freq_gross_pay_ytd
		gen other_emp_ret_svc= avg_emp-freq_gross_pay_ytd
		
		gen other_avg_wages=other_total_wages/other_emp
		gen other_avg_wages_ret_svc=other_total_wages_ret_svc/other_emp_ret_svc
		
		gen ln_avg_gross_pay_ytd=log(avg_gross_pay_ytd)
		gen ln_avg_wages = log(avg_wages)
		gen ln_avg_wages_ret_svc=log(avg_wages_ret_svc)
		gen ln_other_avg_wages=log(other_avg_wages)
		gen ln_other_avg_wages_ret_svc=log(other_avg_wages_ret_svc)
		
		tempfile experiment`experiment'
		save `experiment`experiment''

	 }
	 
	 clear

	foreach experiment in `exp' {	
		append using `experiment`experiment''
	 }
	
	save  "$data/cb/stacked_policy_qcew_q1_wage_bill.dta", replace
	
	* Read in all gap measures datasets, append, store as tempfile
	use "$data/cb/events.dta", clear	
	drop if eventid==4
	
	* Drop overlapping events
	drop if min(months_since_last_policy, months_until_next_policy) <= 6
	
	levelsof eventid, local(exp)
	
	clear
	foreach experiment in `exp' {
		append using "$data/cb/all_gap_measures_`experiment'_qtrly_wage_bill.dta"
		}	
			rename trt_exp first_trt_exp
	
	tempfile all_events_treatment_measures
	save `all_events_treatment_measures'
		
	corr super_gap wage_bill_share_q1
			
	use "$data/cb/stacked_policy_qcew_q1_wage_bill.dta", clear
	drop if etime==0
	gen post=etime>0
	
	gen wage_bill_share=total_gross_pay_ytd/cz_total_qtrly_wages
	gen wage_bill_share_ret_svc=total_gross_pay_ytd/total_qtrly_wages
	
	replace wage_bill_share =. if post==1
	replace wage_bill_share_ret_svc =. if post==1
	
	egen wage_bill_share_pre= mean(wage_bill_share),by(trt_exp cz)
	egen wage_bill_share_ret_svc_pre= mean(wage_bill_share_ret_svc),by(trt_exp cz)
	
	sum avg_gross_pay_ytd,d 
	drop if avg_gross_pay_ytd>r(p99)
	sort trt_exp post cz
	
	gen eventid=trt_exp
	merge m:1 eventid using "$data/cb/events_qcew_expanded.dta", nogen keepusing(eventid_individual) 
	
	* Assuming the string variable is called "strvar"
	gen first_trt_exp = substr(eventid_individual,1,4)
	replace first_trt_exp=subinstr(first_trt_exp," ","",.)
	replace first_trt_exp=substr(first_trt_exp,2,4)
	if length(first_trt_exp)==3{
			replace first_trt_exp=substr(first_trt_exp,1,2) 
	}
	else{
		replace first_trt_exp=substr(first_trt_exp,1,1) 
	}
	
	destring first_trt_exp, replace
	
	merge m:1 cz first_trt_exp using `all_events_treatment_measures', nogen keepusing(T super* employment_share*) keep (3)
	
	gen wt_T = wage_bill_share_pre * T
	egen groupid = group(trt_exp cz)
	tsset groupid post
			
	egen std_wt_T=std(wt_T),by(first_trt_exp)

	gen D = std_wt_T*post

	eststo a: ivreghdfe ln_other_avg_wages (ln_avg_gross_pay_ytd = D), absorb(i.cz#i.trt_exp i.post#i.trt_exp i.cz i.post) vce(cluster cz) first 	
	local qcew_cw_elast=round(_b[ln_avg_gross_pay_ytd],.001)
	local qcew_cw_elast_se=round(_se[ln_avg_gross_pay_ytd],.001)
	
	PrintEst `qcew_cw_elast' "qcew_cw_elast"	"" "%" 4.3f "$figures_tables/text"
	PrintEst `qcew_cw_elast_se' "qcew_cw_elast_se"	"" "%" 4.3f "$figures_tables/text"
	
	foreach outcome of varlist ln_avg_gross_pay_ytd ln_avg_wages ln_other_avg_wages {

			eststo `outcome': reghdfe `outcome' D, absorb(i.cz#i.trt_exp i.post#i.trt_exp i.cz i.post) vce(cluster cz)
				distinct cz if e(sample) == 1
				estadd local Ncz `r(ndistinct)'
				qui sum wt_T if e(sample)==1
				estadd local mean_sg =round(`r(mean)',.0001) 
				estadd local sd_sg  =round(`r(sd)',.0001) 
				estadd local CZFE "Y" 
				estadd local etimeFE "Y"
				sum `outcome' if e(sample) == 1 & post == 0
				estadd local DepVarMean = round(`r(mean)', 0.01) 		
		
		}
	
		* Build table
		
		#d ;
		esttab ln_avg_gross_pay_ytd ln_avg_wages  ln_other_avg_wages 
				using "$figures_tables/table10_qcew_analysis.tex",  
				replace label fragment
				nolines  
				prehead("\begin{threeparttable} \begin{tabular}{lccc} \toprule \toprule & \multicolumn{3}{c}{Dep Var: Log 1st quarter average earnings} \\ Independent variables:") 
				posthead(\cmidrule(lr){2-4}) 
				booktabs								
				nonumbers 
				mtitles("Policy" "All" "All minus policy") 
				collabels(none)  
				cells(b(star fmt(%9.4f)) se(par fmt(%9.4f)) ) 
				starlevels(* 0.1 ** 0.05 *** 0.01) 						
				keep(D)   
				coeflabel(D "Market Gap Proxy X 1(Post)") 
				stats(
					N Ncz  mean_sg sd_sg DepVarMean CZFE etimeFE, 
					fmt(%12.0gc) 
					label("Obs" "CZs" "Mean Market Gap Proxy" "St. Dev. Market Gap Proxy" "Dep Var Mean" "CZ FEs" "Month from event FEs")
				)
				prefoot(\midrule) 
				postfoot(\bottomrule \bottomrule \end{tabular} \end{threeparttable} )
		;
		#d cr
		